# -*-coding:utf-8 -*-
"""
沪深股通标的港股机构持股明细
-转换列表
为:
TradeDate  SECUCODE  SECURITYNAME  HKINS_1 .... HKINS_163  (机构名称对应的code_)对应的数据为 当日持股总量
2020-03-16 000006.SZ 深振业A        0（没有为0）  10400 （股）
"""
import pandas as pd
import numpy as np
import datetime
import common.sql as db
from sqlalchemy.types import  NVARCHAR,VARCHAR,Integer,Float,Date


HK_INS_TABLE_ = "hk_institutions_name"

HK_INS_TABLE_SQL_ = "SELECT index_,code_,name_ from {0}".format(HK_INS_TABLE_)
# 从数据库获取数据，->dataFrame
hk_ins_df = db.getdata_from_sql(HK_INS_TABLE_SQL_)

# 1.得到最终表的columns
columns = hk_ins_df.iloc[0:, 1:2]
# 转化为list
col = np.array(columns).tolist()
cols = [x[0] for x in col]
cols.insert(0, "SECURITYNAME")
cols.insert(0, "SECUCODE")
cols.insert(0, "TradeDate")

# 2.创建新的DataFrame
# 设置数据库的字段类型
dtype_={}
for i in cols:
    if i not in ['TradeDate','SECUCODE','SECURITYNAME']:
        dtype_[i]=Integer
    elif i in ['SECUCODE','SECURITYNAME']:
        dtype_[i]=NVARCHAR(length=500)
    else:
        dtype_[i]=Date
# print(dtype_)
new_df = pd.DataFrame(columns=cols)
new_table_prefix="hk_ins_hold_detail"
tab_=''
# print(cols)

# 3.从sql中读取 要转化的数据
HK_INS_HOLD_DETAIL_INFO_TABLE_ = "gnjtsz_stock_hk_sharehold_detail"
NAME_ = "公牛集团"
COLUMNS_=["SECUCODE","TRADEDATE","SECURITYNAME","PARTICIPANTNAME","SHAREHOLDING"]
HK_INS_HOLD_DETAIL_INFO_SQL_ = "SELECT * FROM `{0}` WHERE SECURITYNAME='{1}';".format(
    HK_INS_HOLD_DETAIL_INFO_TABLE_, NAME_)

hk_ins_info_df=db.getdata_from_sql_query(HK_INS_HOLD_DETAIL_INFO_SQL_,COLUMNS_)

# 4.处理数据
# 新数据添加行：以字典添加：
new_append_dict={}
for v in cols:
    new_append_dict[v]=0

#new_df.append(new_append_dict, ignore_index=True )
tem_group=hk_ins_info_df.groupby('TRADEDATE')
for d,v in tem_group:
    new_append_=new_append_dict.copy()
    new_append_[cols[0]]=d #交易时间
    new_append_[cols[1]]=v.iloc[1,0] # 证券代码
    new_append_[cols[2]]=v.iloc[1,2] #证券名称
    tab_=v.iloc[1,0]
    # print(d)
    for index,rows in v.iterrows():
        # print(index)
        # print(rows['PARTICIPANTNAME'])
        x2=rows['PARTICIPANTNAME']
        v1=rows['SHAREHOLDING']
        df_=hk_ins_df.query("name_=='{0}'".format(x2))
        key_=df_.code_.values[0]
        new_append_dict[key_]=v1
    # print(new_append_)
    new_df=new_df.append(new_append_, ignore_index=True )

# print(hk_ins_info_df.head(20))

tab_1=tab_.split(".")
tab_="".join(tab_1)
tab_=tab_.lower()
# print(tab_)

# print(new_df.dtypes)
db.dataframe_tosql_(new_df,new_table_prefix+tab_,dictdtype=dtype_)
# print(new_df)
